package DBServer

import (
	"UpgradeServer/Common"

	"database/sql"
	"fmt"
	"log"
	"os"
	"strconv"

	_ "github.com/go-sql-driver/mysql"
)

/// 数据库服务器
/// 1. 初始化数据库
/// 2. 查询staff表里用户信息客户端用于登录验证
/// 3. 查询cliver表里文件信息客户端用于判断是否需要升级
/// 4. 插入升级文件信息到cliver表里用于客户端升级

type DBServer struct {
	mysql *sql.DB `default:"nil"`
}

// staff info
type StaffInfo struct {
	Id       int64
	UserName string
	UserUid  string
	UserRole int
	Salt     string
	Crypt    string
	Status   int
}

// file info
type FileInfo struct {
	Id           int64
	Version      string
	Desc         string
	CheckSum     string
	FilePath     string
	UploadUserID int64
}

// init mysql db
func InitDBServer() *DBServer {
	var err error = nil
	m := new(DBServer)
	connstr := Common.MysqlUser + ":" + Common.MysqlPass + "@(" + Common.MySqlAddress + ":" + strconv.Itoa(Common.MySqlPort) + ")/moriak?charset=utf8&parseTime=true"
	m.mysql, err = sql.Open("mysql", Common.MysqlUser+":"+Common.MysqlPass+"@("+Common.MySqlAddress+":"+strconv.Itoa(Common.MySqlPort)+")/moriak?charset=utf8&parseTime=true")
	if err != nil {
		fmt.Println("Open mysql error:", err, connstr)
		log.Println("Open mysql error:", err, connstr)
		os.Exit(-1)
		return nil
	}
	log.Println("Init mysql success")
	//defer m.mysql.Close()
	return m
}

func (m *DBServer) ConnectDB() error {
	var err error = nil
	m.mysql, err = sql.Open("mysql", Common.MysqlUser+":"+Common.MysqlPass+"@("+Common.MySqlAddress+":"+strconv.Itoa(Common.MySqlPort)+")/moriak?charset=utf8&parseTime=true")
	if err != nil {
		log.Println("Open mysql error: ", err)
		return err
	}
	//defer m.mysql.Close()
	return nil
}

func (m *DBServer) Close() {
	m.mysql.Close()
}

func (m *DBServer) CheckDBConn() error {
	err := m.mysql.Ping()
	if err != nil {
		log.Println("Ping mysql error: ", err)
		// 重连数据
		return m.ConnectDB()
	}
	return nil
}

// query staff id by username
func (m *DBServer) QueryStaff(userName string) *StaffInfo {
	if m.CheckDBConn() != nil {
		log.Printf("QueryStaff CheckDBConn error")
		return nil
	}

	defer func() {
		if r := recover(); r != nil {
			log.Println("QueryStaff recover error: ", r)
		}
	}()

	var sql = "SELECT id, name, uid, role, salt, crypt, status FROM moriak.staff where uid = '" + userName + "' LIMIT 1"
	log.Println(sql)
	rows, err := m.mysql.Query(sql)
	if err != nil {
		log.Println("query staff error: ", err)
		return nil
	}

	var rec = new(StaffInfo)
	if rows.Next() {
		err = rows.Scan(&rec.Id, &rec.UserName, &rec.UserUid, &rec.UserRole, &rec.Salt, &rec.Crypt, &rec.Status)
		if err != nil {
			log.Println("query staff error: ", err)
			return nil
		}
	}
	defer rows.Close()
	log.Println(rec.Id, rec.UserName, rec.UserUid, rec.UserRole, rec.Salt, rec.Crypt, rec.Status)
	return rec
}

// query file info by
func (m *DBServer) QueryFileInfo() *FileInfo {
	if m.CheckDBConn() != nil {
		log.Printf("QueryFileInfo CheckDBConn error")
		return nil
	}

	defer func() {
		if r := recover(); r != nil {
			log.Println("QueryStaff recover error: ", r)
		}
	}()

	sql := "select id,uid,dscr,cksm, inspath from moriak.cliver order by id desc limit 1"
	var finfo = new(FileInfo)
	rows, err := m.mysql.Query(sql)
	if err != nil {
		log.Println("query file info error: ", err)
		return nil
	}
	if rows.Next() {
		err = rows.Scan(&finfo.Id, &finfo.Version, &finfo.Desc, &finfo.CheckSum, &finfo.FilePath)
		if err != nil {
			log.Println("query file info error: ", err)
			return nil
		}
	}
	defer rows.Close()

	return finfo
}

func (m *DBServer) InsertFileInfo(fileInfo *FileInfo) (int64, string) {
	if m.CheckDBConn() != nil {
		log.Printf("InsertFileInfo CheckDBConn error")
		return -1, "InsertFileInfo CheckDBConn error"
	}

	defer func() {
		if r := recover(); r != nil {
			log.Println("DeleteFileInfo recover error: ", r)
		}
	}()

	//insert into moriak.cliver(uid,dscr,cksm, inspath,create_time,modify_time) value('1.0.0.1', 'moriak version1.0.0.1','123456', './', sysdate(), sysdate())
	sqlFormat := "insert into moriak.cliver(uid,dscr,cksm, inspath, creator_user_id, create_time,modify_time) "
	sqlFormat += "value('%s', '%s','%s', '%s', %d, sysdate(), sysdate())"
	insertSql := fmt.Sprintf(sqlFormat, fileInfo.Version, fileInfo.Desc,
		fileInfo.CheckSum, fileInfo.FilePath, fileInfo.UploadUserID)
	log.Println(insertSql)
	res, err := m.mysql.Exec(insertSql)
	if err != nil {
		errStr := fmt.Sprintf("insert file info error:%s,%s ", err, res)
		log.Println("insert file info error: ", err, res)
		return -1, errStr
	}

	id, _ := res.LastInsertId()
	log.Println("insert file info success, id: ", id)

	return id, ""
}

// delete file info by id
func (m *DBServer) DeleteFileInfo(fid int64) {
	if m.CheckDBConn() != nil {
		log.Printf("DeleteFileInfo CheckDBConn error")
		return
	}

	defer func() {
		if r := recover(); r != nil {
			log.Println("QueryStaff recover error: ", r)
		}
	}()

	sql := fmt.Sprintf("delete from moriak.cliver where id = %d", fid)
	m.mysql.Exec(sql)
}
